package kursynbp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CountryDAO extends Country {

	public CountryDAO(String name) {
		super(name);
	}
	
	public static Void persist(Connection connection, Country c) {
		try {
			
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

	    	Statement statement1 = connection.createStatement();
	    	ResultSet resultSet1 = statement1.executeQuery("SELECT id FROM country WHERE name = \'" + c.getName() + "\'");
	    	if (!resultSet1.next()) {
	    		// persist in db
	    		Statement statement2 = connection.createStatement();
	    		statement2.execute("INSERT INTO country (name) VALUES (\'" + c.getName() + "\')");
	    	}

		} catch (SQLException e) {
			System.out.println("Unable to connect to SQL database.");
		}
    	return null;
	}
	
	public void addCurrency(Connection connection, ExchangeDAO curr) {
		
    	try {
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

			Statement statement1 = connection.createStatement();
			ResultSet resultSet1 = statement1.executeQuery("SELECT id FROM country WHERE name = \'" + getName() + "\'");
			resultSet1.next();
			Integer cid = resultSet1.getInt("id");

			Statement statement2 = connection.createStatement();
			ResultSet resultSet2 = statement2.executeQuery("SELECT id FROM currency WHERE code = \'" + curr.getCode() + "\'");
			resultSet2.next();
			Integer currId = resultSet2.getInt("id");
			
			Statement statement3 = connection.createStatement();
			ResultSet resultSet3 = statement3.executeQuery("SELECT id FROM country_currency WHERE country_id = " + cid.toString() + " AND currency_id = " + currId.toString());
			if (resultSet3.next()) return;
			
			Statement statement4 = connection.createStatement();
			statement4.execute("INSERT INTO country_currency (country_id, currency_id) VALUES (" + cid.toString() + ", " + currId.toString() + ")");
		} catch (SQLException e) {
			throw new DBPersistorException("Could not persist country object");
		}
	}
	
	public static List<CountryDAO> findCountriesWithMultipleCurrencies(Connection connection) {
		
		ArrayList<CountryDAO> retList = new ArrayList<CountryDAO>();

		try {
			// Connection connection = DBConnectionFactory.getInstance().getConnection();

			Statement statement2 = connection.createStatement();
			ResultSet resultSet2 = statement2.executeQuery("SELECT id, name FROM country WHERE id = (SELECT country_id FROM country_currency GROUP BY country_id HAVING count(currency_id) > 1)");

			while(resultSet2.next()) {
				CountryDAO country = new CountryDAO(resultSet2.getString("name"));
				retList.add(country);
			}
		} catch (SQLException e) {
			throw new DBPersistorException("Could not retrieve country object");
		}
    	
    	return retList;
	}
}
